package com.cocoblue.nextjslearn.repo;

import com.cocoblue.nextjslearn.dto.InvoiceDTO;
import com.cocoblue.nextjslearn.entity.Invoice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface InvoiceRepository extends JpaRepository<Invoice, Long> {

    @Query(value = """
            select c.name as name, c.email as email, c.image_url as imageUrl, i.id as id, i.amount as amount
            from INVOICE i join CUSTOMER c on i.customer_id = c.id
            order by i.date desc
            limit 5
            """, nativeQuery = true)
    List<InvoiceDTO> findLatestInvoice();

    @Query(value = """
            SELECT
              invoice.id,
              invoice.amount,
              invoice.date,
              invoice.status,
              customer.name,
              customer.email,
              customer.image_url as imageUrl
            FROM invoice
            JOIN customer ON invoice.customer_id = customer.id
            WHERE
              customer.name LIKE concat('%',:query,'%') OR
              customer.email LIKE concat('%',:query,'%') OR
              invoice.amount LIKE concat('%',:query,'%') OR
              invoice.date LIKE concat('%',:query,'%') OR
              invoice.status LIKE concat('%',:query,'%')
            ORDER BY invoice.date DESC
            LIMIT :limit OFFSET :offset
                 """, nativeQuery = true)
    List<InvoiceDTO> findInvoiceByFilter(@Param("query") String query,
                                         @Param("limit") int limit,
                                         @Param("offset") int offset);

    @Query(value = """
            SELECT COUNT(*)
                FROM invoice
                JOIN customer ON invoice.customer_id = customer.id
            WHERE
              customer.name LIKE concat('%',:query,'%') OR
              customer.email LIKE concat('%',:query,'%') OR
              invoice.amount LIKE concat('%',:query,'%') OR
              invoice.date LIKE concat('%',:query,'%') OR
              invoice.status LIKE concat('%',:query,'%')
            """, nativeQuery = true)
    int findInvoicePageByFilter(@Param("query") String query);


}